﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace ZhuGe.DAL.SQLite
{
    /// <summary>
    /// 操作sqlite的基础类
    /// </summary>
    public class SqliteOperate
    {
        #region 全局属性

        public const String GetAllTableSQL = "select * from sqlite_master where type='table'";   //获得数据库所有表

        private const String DBPWDKEYS = "SQLITEBYSKY";                                          //数据库加密密钥

        #endregion

        #region 私有属性

        /// <summary>
        /// 连接对象
        /// </summary>
        private static SQLiteConnection conn;

        /// <summary>
        /// sqlite数据库的地址
        /// </summary>
        private static string sqlitePath = "E:/changandb.db";

        /// <summary>
        /// sqlite密码
        /// </summary>
        private static string sqlitePwd = "changandbpwd";

        #endregion

        #region 全局方法

        /// <summary>
        /// 从数据库查询数据
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="par"></param>
        /// <param name="pv"></param>
        /// <returns></returns>
        public static DataTable GetDataFromDB(string cmd, string[] par, string[] pv)
        {
            DataTable ds = new DataTable();
            try
            {
                if (!OpenDB())
                {
                    return ds;
                }
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd, conn);
                if (par != null && pv != null && par.Length > 0 && par.Length == pv.Length)
                {
                    for (int i = 0; i < par.Length; i++)
                    {
                        sda.SelectCommand.Parameters.AddWithValue(par[i], pv[i]);
                    }
                }
                sda.Fill(ds);
            }
            catch (Exception e)
            {
                //PublicFunction.MessBoxShow(MessageBasket.OperateDbError);
                throw new Exception(e.Message);
            }
            finally
            {
                //conn.Close();
            }
            return ds;
        }

        /// <summary>
        /// 添加、修改、删除 数据的方法
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="par"></param>
        /// <param name="pv"></param>
        /// <returns></returns>
        public static int CmdDataSource(string cmd, string[] par, string[] pv)
        {
            int iResult = 0;
            try
            {
                if (!OpenDB())
                {
                    return iResult;
                }
                SQLiteCommand cm = new SQLiteCommand(cmd, conn);
                if (par != null && pv != null && par.Length > 0 && par.Length == pv.Length)
                {
                    for (int i = 0; i < par.Length; i++)
                    {
                        cm.Parameters.AddWithValue(par[i], pv[i]);
                    }
                }
                iResult = cm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                iResult = 0;
                throw new Exception(e.Message);
            }
            finally
            {
                //conn.Close();
            }
            return iResult;
        }


        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <returns></returns>
        public static void CloseDB()
        {
            conn.Close();
        }


        /// <summary>
        /// 打开数据库
        /// </summary>
        private static void OpenSqlDB()
        {
            try
            {
                conn = new SQLiteConnection();
                SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
                connstr.DataSource = sqlitePath;
                if (sqlitePwd.Length > 0)//只有当密码不为空才设置密码
                {
                    connstr.Password = sqlitePwd;//设置密码，SQLite ADO.NET实现了数据库密码保护 
                }
                conn.ConnectionString = connstr.ToString();
                conn.Open();
            }
            catch (Exception ex)
            {
                string errmsg = ex.Message;
            }
        }

        #endregion

        #region 私有方法

        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <returns></returns>
        public static bool OpenDB()
        {
            bool reBool = true;
            try
            {
                if (conn == null || conn.State != ConnectionState.Open)
                {
                    conn = new SQLiteConnection();
                    SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
                    connstr.DataSource = sqlitePath;
                    if (sqlitePwd.Length > 0)//只有当密码不为空才设置密码
                    {
                        connstr.Password = sqlitePwd;//设置密码，SQLite ADO.NET实现了数据库密码保护 
                    }
                    conn.ConnectionString = connstr.ToString();
                    conn.Open();
                    reBool = true;
                }
            }
            catch (Exception ex)
            {
                string errmsg = ex.Message;
            }
            return reBool;
        }

        #endregion

        #region 设置数据库路径,并打开数据库

        /// <summary>
        /// 设置数据库路径,并打开数据库
        /// </summary>
        /// <param name="pDBFilePath">Like "E:/changandb.db"</param>
        /// <param name="pPWD">明文密码</param>
        public static void SetDBFilePath(String pDBFilePath, String pPWD)
        {
            sqlitePath = pDBFilePath;
            sqlitePwd = pPWD;
            //sqlitePwd = DESEncrypt.Encrypt(pPWD, DBPWDKEYS);

            OpenSqlDB();
        }

        #endregion

    }
}
